﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class DSTAB05DOCData : BaseFunction
    {

        byte[] b;
        string value;
        public DSTAB05DOCData()
            : base()
        {

        }
        public DSTAB05DOCData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetABPOS(DataRow dwUI)
        {
            string TableName = "ABPOS";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD,FPOS;
            FKRS = dwUI["FKRS"].ToString().Trim();
            FLFD = dwUI["FLFD"].ToString().Trim();
            FPOS = dwUI["FPOS"].ToString().Trim();
            try
            {
                 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select distinct A.ABPAUFKRS,A.ABPAUFLFD,A.ABPAUFPOS,A.ABPAUFART, "
                    + " A.ABPART,A.ABPVAR,A.ABPFARBE,SUBSTR(D.SDDATEN,2,40) AS FINISHING, "
                    + " A.ABPSOLLMENGE2*0.01 AS QTY,A.ABPNEUDAT,A.ABPNEUBEN, "
                    + " A.ABPENDDAT,TRIM(A.ABPANRKR)||'-'||TO_NUMBER(A.ABPALFDNR)||E.APKSPEZIF AS WORKFLOW, "
                    + " TO_NUMBER(F.VKKKUNDNR) AS CUSTOMER, '' AS REMARK, "
                    + " TRIM(B.ABAVKAUFKRS)||TO_NUMBER(B.ABAVKAUFLFD)||'-'||TO_NUMBER(B.ABAVKAUFPOS) AS SO, "
                    + " G.WARWBRT*0.1 AS GWID,G.WARFDDIK*0.01||'*'||G.WARFDDIS*0.01 GDDIK,'' as COUNT, "
                    + " G.WAFFDDIK*0.01||'*'||G.WAFFDDIS*0.01 AS FDDIK,(G.WAFBRTU-G.WAFBRTUT)*0.1||'-'||(G.WAFBRTU+G.WAFBRTUT)*0.1 AS FWID "
                    + " FROM ABPOS A,ABAUFM B,WAFARBEN C,SDDAT D,APKOPF E,VKKOPF F,WASTAMM G "
                    + " WHERE A.ABPMANDANT = B.ABAMANDANT AND A.ABPAUFKRS = B.ABAAUFKRS "
                    + " AND A.ABPAUFLFD = B.ABAAUFLFD AND A.ABPAUFPOS = B.ABAAUFPOS "
                    + " AND A.ABPMANDANT = C.WAFMANDANT AND A.ABPART = C.WAFART "
                    + " AND NVL(TRIM(A.ABPVAR),'-') = NVL(TRIM(C.WAFVAR),'-') AND A.ABPBESCHICH = C.WAFZUSATZ "
                    + " AND C.WAFMANDANT = SUBSTR(SDKEY,1,2) AND C.WAFRUESTGRP = SUBSTR(SDKEY,3,3) "
                    + " AND A.ABPMANDANT = E.APKMANDANT AND A.ABPANRKR = E.APKANRKRS AND A.ABPALFDNR = E.APKALFDNR "
                    + " AND B.ABAMANDANT = F.VKKMANDANT AND B.ABAVKAUFKRS = F.VKKAUFKRS AND B.ABAVKAUFLFD = F.VKKAUFLFD "
                    + " AND A.ABPMANDANT = G.WAMANDANT AND A.ABPKUNDNR = G.WAKUNDNR AND A.ABPWERK = G.WAWERK "
                    + " AND A.ABPABT = G.WAABT AND A.ABPART = G.WAART AND NVL(TRIM(A.ABPVAR),'-') = NVL(TRIM(G.WAVAR),'-') "
                    + " AND C.WAFZUART = 'B' AND D.SDSA = '0275' "
                    + " AND A.ABPAUFART IN ('VF','VA','AM') AND B.ABAAPOS > '0001' "
                    + " AND B.ABAVKAUFTYP = 'K' AND F.VKKSA <>'AJ'  "
                    + " AND A.ABPAUFKRS ='" + FKRS + "' AND A.ABPAUFLFD = '" + FLFD + "' AND A.ABPAUFPOS = '" + FPOS + "' "
                    ;
                
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName; 

                    byte[] b;
                    string value; 
                    string sTemp = ""; 
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "FINISHING";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "WORKFLOW";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        dwTemp["REMARK"] = GetCommentsFromTXTIND("ABP", FKRS+FLFD+FPOS);
                        GetCOUNT(dwTemp);

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetABFOLGE(DataRow dwUI)
        {
            string TableName = "ABFOLGE";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD, FPOS;
            FKRS = dwUI["FKRS"].ToString().Trim();
            FLFD = dwUI["FLFD"].ToString().Trim();
            FPOS = dwUI["FPOS"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.ABFAFOLGE,A.ABFAGANG,A.ABFBZAGANG FROM ABFOLGE A  "
                    + " WHERE A.ABFAUFKRS = '" + FKRS + "' AND A.ABFAUFLFD ='" + FLFD + "' AND A.ABFAUFPOS = '" + FPOS + "' "
                    + " ORDER BY 1 ";

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "ABFBZAGANG";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        
                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWBBEW(DataRow dwUI)
        {
            string TableName = "WBBEW";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD, FPOS,TP;
            FKRS = dwUI["WBBAUFKRS"].ToString().Trim();
            FLFD = dwUI["WBBAUFLFD"].ToString().Trim();
            FPOS = dwUI["WBBAUFPOS"].ToString().Trim();
            TP = dwUI["WBBTP"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT MAX(WBEWBUCHDAT) AS STARTDAT FROM WBBEW "
                    + " WHERE WBEWMANDANT = '00' AND WBEWBS = '61' AND WBEWAUFTYP = 'V' "
                    + " AND WBEWAUFKRS = '" + FKRS + "' AND WBEWAUFLFD = '" + FLFD + "' "
                    + " AND WBEWAUFPOS =  '" + FPOS + "' AND  WBEWTP =  '" + TP + "' "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        dwUI["STARTDAT"] =  dwTemp["STARTDAT"].ToString().Trim();

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWBTP(DataRow dwUI)
        {
            string TableName = "WBTP";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD, FPOS;
            FKRS = dwUI["FKRS"].ToString().Trim();
            FLFD = dwUI["FLFD"].ToString().Trim();
            FPOS = dwUI["FPOS"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBTP,'' AS STARTDAT,SUM(1) AS PENUM,SUM(WBBSSTKLG*0.001) AS QTY "
                       + " FROM WBBST WHERE WBBMANDANT = '00' AND WBBAUFTYP = 'V' "
                       + " AND WBBAUFKRS = '" + FKRS + "' AND WBBAUFLFD='" + FLFD + "' AND WBBAUFPOS='" + FPOS + "' "
                       + " GROUP BY  WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBTP ORDER BY 4"
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        GetWBBEW(dwTemp);

                    }
                   
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWBBST(DataSet dsTarget, DataRow dwUI)
        {
            string TableName = "WBBST";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD, FPOS,WBBTP;
            FKRS = dwUI["WBBAUFKRS"].ToString().Trim();
            FLFD = dwUI["WBBAUFLFD"].ToString().Trim();
            FPOS = dwUI["WBBAUFPOS"].ToString().Trim();
            WBBTP = dwUI["WBBTP"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBTP,'' AS STARTDAT,SUM(1) AS PENUM,SUM(WBBSSTKLG*0.001) AS QTY "
                       + " FROM WBBST WHERE WBBMANDANT = '00' AND WBBAUFTYP = 'V' "
                       + " AND WBBAUFKRS = '" + FKRS + "' AND WBBAUFLFD='" + FLFD + "' AND WBBAUFPOS='" + FPOS + "' "
                       + " AND WBBTP = '" + WBBTP + "'  GROUP BY  WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBTP "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        GetWBBEW(dwTemp);

                    }

                }
                dsTarget.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetCOUNT(DataRow dwUI)
        {
            string TableName = "COUNT";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string ART,VAR;
            ART = dwUI["ABPART"].ToString();
            VAR = dwUI["ABPVAR"].ToString();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT WAART,WAVAR,REPLACE(STRAGG(TRIM(MARK)) "
                    + " OVER(PARTITION BY WAART,WAVAR ),',','X') AS COUNT  "
                    + " FROM (select DISTINCT a.waart,a.wavar,b.wapbis, "
                    + " '('||trim(B.WAPBIS)||' '||Replace(stragg(trim(substr(c.qpmbez,16)))  "
                    + " over(partition by a.waart,a.wavar,b.wapbis),',','+')||')' "
                    + "  as mark "
                    + " from wastamm a,waprof b,qpmerkm c "
                    + " where a.wamandant = b.wapmandant and a.wakundnr = b.wapkundnr "
                    + " and a.wawerk = b.wapwerk and a.waabt = b.wapabt and a.waart = b.wapart "
                    + " and a.wavar = b.wapvar and b.wapwakz = 'F' "
                    + " and a.waart = '"+ART+"' and a.wavar = '  ' "
                    + " and b.wapmandant = c.qpmmandant and b.wapanfart = c.qpmmerkm "
                    + " and b.wapbis in ('J','W') order by 3) " 
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "COUNT";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        dwUI["COUNT"] = value;

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try 
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetABPOS(dwUI);
                setReturn.Merge(setResult);
                setResult = GetABFOLGE(dwUI);
                setReturn.Merge(setResult);
                setResult = GetWBTP(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetABPOS1(DataRow dwUI)
        {
            string TableName = "ABPOS";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD, FPOS;
            FKRS = dwUI["FKRS"].ToString().Trim();
            FLFD = dwUI["FLFD"].ToString().Trim();
            FPOS = dwUI["FPOS"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select distinct A.ABPAUFKRS,A.ABPAUFLFD,A.ABPAUFPOS,A.ABPAUFART, "
                    + " A.ABPART,A.ABPVAR,A.ABPFARBE,SUBSTR(D.SDDATEN,2,40) AS FINISHING, "
                    + " A.ABPSOLLMENGE2*0.01 AS QTY,A.ABPNEUDAT,A.ABPNEUBEN, "
                    + " A.ABPENDDAT,TRIM(A.ABPANRKR)||'-'||TO_NUMBER(A.ABPALFDNR)||E.APKSPEZIF AS WORKFLOW, "
                    + " TRIM(F.KDNAME1) AS CUSTOMER, '' AS REMARK, "
                    + " TRIM(a.abpaufkrs) || TO_NUMBER(a.abpauflfd) || '-' ||TO_NUMBER(a.abpaufpos) AS SO, "
                    + " G.WARWBRT*0.1 AS GWID,G.WARFDDIK*0.01||'*'||G.WARFDDIS*0.01 GDDIK,G.WAFBEM as COUNT, "
                    + " G.WAFFDDIK*0.01||'*'||G.WAFFDDIS*0.01 AS FDDIK,(G.WAFBRTU-G.WAFBRTUT)*0.1||'-'||(G.WAFBRTU+G.WAFBRTUT)*0.1 AS FWID "
                    + " FROM ABPOS A,WAFARBEN C,SDDAT D,APKOPF E,KDSTAMM F,WASTAMM G "
                    + " WHERE A.ABPMANDANT = C.WAFMANDANT AND A.ABPART = C.WAFART "
                    + " AND NVL(TRIM(A.ABPVAR),'-') = NVL(TRIM(C.WAFVAR),'-') AND A.ABPBESCHICH = C.WAFZUSATZ "
                    + " AND C.WAFMANDANT = SUBSTR(SDKEY,1,2) AND C.WAFRUESTGRP = SUBSTR(SDKEY,3,3) "
                    + " AND A.ABPMANDANT = E.APKMANDANT AND A.ABPANRKR = E.APKANRKRS AND A.ABPALFDNR = E.APKALFDNR "
                    + " AND A.ABPMANDANT = F.KDMANDANT AND A.ABPKUNDNR = F.KDKUNDNR  "
                    + " AND A.ABPMANDANT = G.WAMANDANT AND A.ABPKUNDNR = G.WAKUNDNR AND A.ABPWERK = G.WAWERK "
                    + " AND A.ABPABT = G.WAABT AND A.ABPART = G.WAART AND NVL(TRIM(A.ABPVAR),'-') = NVL(TRIM(G.WAVAR),'-') "
                    + " AND C.WAFZUART = 'B' AND D.SDSA = '0275' "
                    + " AND A.ABPAUFART IN ('VF','VA','AM') "
                    + " AND A.ABPAUFKRS ='" + FKRS + "' AND A.ABPAUFLFD = '" + FLFD + "' AND A.ABPAUFPOS = '" + FPOS + "' "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "FINISHING";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "WORKFLOW";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "CUSTOMER";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        dwTemp["REMARK"] = GetCommentsFromTXTIND("ABP", FKRS + FLFD + FPOS);
                        //GetCOUNT(dwTemp);

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildData1(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetABPOS1(dwUI);
                setReturn.Merge(setResult);
                setResult = GetABFOLGE(dwUI);
                setReturn.Merge(setResult);
                setResult = GetWBTP(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}